﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;


namespace HRdex_Final
{
    public partial class HRMainMaster : System.Web.UI.MasterPage
    {
        DataTable dtbl = new DataTable("dt");
        SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");

        string SessionID()
        {
           return Session["EmployeeID"].ToString();
        }

        protected void Page_Load(object sender, EventArgs e)
        {

            SqlConnection conn = new SqlConnection("Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\HRdexDB.mdf;Integrated Security=True;User Instance=True");
            SqlCommand cmdAdmin = new SqlCommand("SELECT        formEmployees.EmployeeID, catUserLevel.catUserLevelName AS [User Level], catPositions.catPositionsName AS Position, " +
                         " catDepartment.DepartmentName AS Department, catCollege.CollegeName AS Office " +
                         " FROM            formEmployees INNER JOIN " +
                         " catDepartment ON formEmployees.DeptID = catDepartment.DeptID INNER JOIN " +
                         " catCollege ON formEmployees.CollegeID = catCollege.CollegeID INNER JOIN " +
                         " catPositions ON formEmployees.catPositionsID = catPositions.catPositionsID INNER JOIN " +
                         " catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE formEmployees.EmployeeID = " + SessionID(), conn);
            SqlDataAdapter daAdmin = new SqlDataAdapter(cmdAdmin);
            DataTable dtAdmin = new DataTable("dtAdmin");
            daAdmin.Fill(dtAdmin);

            SqlCommand cmd = new SqlCommand("SELECT formEmployees.EmployeeID, formEmployees.Password, catUserLevel.catUserLevelName, formEmployees.Lastname+', '+formEmployees.FirstName+' '+formEmployees.MiddleName AS Name FROM formEmployees INNER JOIN  catUserLevel ON formEmployees.UserLevelID = catUserLevel.UserLevelID WHERE (formEmployees.EmployeeID = " + SessionID() + ")", conn);
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(dtbl);
            hlProfile.Text = dtbl.Rows[0].ItemArray[3].ToString();
            if (dtbl.Rows[0].ItemArray[2].ToString() == "Admin" || dtbl.Rows[0].ItemArray[2].ToString() == "SubAdmin" || dtbl.Rows[0].ItemArray[2].ToString() == "Staff")
            {
                AdminNavigation.Visible = true;

                EmpNavigation.Visible = false;
                if (approvepos(SessionID()) == "Head" || approvepos(SessionID()) == "Dean" || approvepos(SessionID()) == "Chairperson" || approvepos(SessionID()) == "Director")
                {
                    int totalapprove = livnotif(SessionID(), approvepos(SessionID())) + subnotif(SessionID(), approvepos(SessionID())) + flexnotif(SessionID(), approvepos(SessionID())) + cbanotif(SessionID(), approvepos(SessionID()));
                    AdminNavigation.Items[7].Text = "Forms(" + totalapprove + ")";
                }
                
            }
            else
            {
                EmpNavigation.Visible = true;
                AdminNavigation.Visible = false;
                if (approvepos(SessionID()) == "Head" ||approvepos(SessionID()) == "Dean"||approvepos(SessionID()) == "Chairperson"||approvepos(SessionID()) == "Director")
                {
                    int totalapprove = livnotif(SessionID(), approvepos(SessionID())) + subnotif(SessionID(), approvepos(SessionID())) + flexnotif(SessionID(), approvepos(SessionID()));
                    EmpNavigation.Items[4].Text = "Forms(" + totalapprove + ")";
                }
            }

            AdminNavigation.Items[2].Text = "Message(" + msgnotif(SessionID()) + ")";
            EmpNavigation.Items[1].Text = "Message(" + msgnotif(SessionID()) + ")";
            
        }
        string msgnotif(string empid)
        {
            string scmd = "SELECT COUNT(Readed) AS unread FROM  formMessegeSender WHERE  (EmployeeID = " + empid + ") AND (Readed <> 1)";
            SqlDataAdapter cmd = new SqlDataAdapter(scmd, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            

            return dtbl.Rows[0][0].ToString();
        }

        protected void Button1_Click(object sender, EventArgs e)
        {
            Session.Clear();
            Response.Redirect("/Default.aspx");
        }


        //int formnotif(string empid, string usrlvl, string typ)
        //{
        //    string sqlc, sqlc2, sqlc3, sqlc1;
        //    if (typ == "Faculty")
        //    {
        //             if (usrlvl == "Head" || usrlvl == "Chairperson")
        //        {
        //            //sqlc = "SELECT COUNT(formFlexi.HeadApproval) + COUNT(formLeave.HeadApproval) + COUNT(formSubstitution.HeadApproval) AS totalapprove " +
        //            //    "FROM formFlexi CROSS JOIN formSubstitution CROSS JOIN formLeave " +
        //            //    "WHERE (DeptID IN (SELECT catDepartment.DeptID FROM catDepartment INNER JOIN formEmployees ON catDepartment.DeptID = formEmployees.DeptID " +
        //            //    "WHERE (formEmployees.EmployeeID = " + empid + "))) AND " +
        //            //    "(formFlexi.HeadApproval IS NULL) OR (formLeave.HeadApproval IS NULL) OR (formSubstitution.HeadApproval IS NULL)";

        //            sqlc = "SELECT LeaveID FROM formLeave WHERE (DepartmentID IN " +
        //                     "(SELECT formLeave_1.DepartmentID FROM formLeave AS formLeave_1 INNER JOIN " +
        //                       "formEmployees ON formLeave_1.EmployeeID = formEmployees.EmployeeID" +
        //                       "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";

        //            sqlc1 = "SELECT SubstitutionID FROM formSubstitution WHERE (DepartmentID IN " +
        //                     "(SELECT formLeave_1.DepartmentID FROM formSubstitution AS formLeave_1 INNER JOIN " +
        //                       "formEmployees ON formLeave_1.EmployeeID = formEmployees.EmployeeID" +
        //                       "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
                

        //        }
        //        else if (usrlvl == "Dean" || usrlvl == "Director")
        //        {
        //            //sqlc = "SELECT COUNT(formFlexi.DeanApproval) + COUNT(formLeave.DeanApproval) + COUNT(formSubstitution.DeanApproval) AS totalapprove " +
        //            //    "FROM formFlexi CROSS JOIN formSubstitution CROSS JOIN formLeave" +
        //            //    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
        //            //    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (formFlexi.HeadApproval IS NOT NULL) OR (formLeave.HeadApproval IS NOT NULL) OR " +
        //            //    "(formSubstitution.HeadApproval IS NOT NULL) AND (formFlexi.DeanApproval IS NULL) AND (formSubstitution.DeanApproval IS NULL) AND " +
        //            //    "(formLeave.DeanApproval IS NULL)";
        //            sqlc = "SELECT LeaveID FROM formLeave WHERE (DepartmentID IN " +
        //                     "(SELECT formLeave_1.DepartmentID FROM formLeave AS formLeave_1 INNER JOIN " +
        //                       "formEmployees ON formLeave_1.EmployeeID = formEmployees.EmployeeID" +
        //                       "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";

        //            sqlc1 = "SELECT SubstitutionID FROM formLeave WHERE (DepartmentID IN " +
        //                     "(SELECT formLeave_1.DepartmentID FROM formSubstitution AS formLeave_1 INNER JOIN " +
        //                       "formEmployees ON formLeave_1.EmployeeID = formEmployees.EmployeeID" +
        //                       "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
        //        }
        //    }

           
        //    else
        //    {
        //       // sqlc = "SELECT  COUNT(formCBA.HRApproval) + COUNT(formFlexi.HRApproval) + COUNT(formLeave.HRApproval) + COUNT(formSubstitution.HRApproval) AS totalapprove" +
        //       //"FROM  formCBA CROSS JOIN formFlexi CROSS JOIN formSubstitution CROSS JOIN formLeave" +
        //       //"WHERE (formCBA.HRApproval IS NULL) OR (formFlexi.DeanApproval IS NOT NULL) OR (formLeave.DeanApproval IS NOT NULL) OR (formSubstitution.DeanApproval IS NOT NULL)";
        //        sqlc = "";
        //    }


        //    //
        //    SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

        //    DataTable dtbl = new DataTable();

        //    cmd.Fill(dtbl);



        //    return dtbl.Rows.Count;
        //}
        int subnotif(string empid, string usrlvl)
        {
            string sqlc;
            if (usrlvl == "Head" || usrlvl == "Chairperson")
            {
                sqlc = "SELECT SubstitutionID FROM formSubstitution WHERE (DeptID IN  (SELECT catDepartment.DeptID " +
                       "FROM catDepartment INNER JOIN formEmployees ON catDepartment.DeptID = formEmployees.DeptID " +
                        "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
            }
            else if (usrlvl == "Dean" || usrlvl == "Director")
            {
                sqlc = "SELECT SubstitutionID FROM formSubstitution " +
                    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN " +
                    "formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
                    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (DeanApproval IS NULL) AND (HeadApproval = 'Approved')";
            }
            else
            {
                sqlc = "SELECT SubstitutionID FROM formSubstitution WHERE (HRApproval = 'Approved') AND (DeanApproval IS NOT NULL)";
            }


            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        int livnotif(string empid, string usrlvl)
        {
            string sqlc;
            if (usrlvl == "Head" || usrlvl == "Chairperson")
            {
                sqlc = "SELECT LeaveID FROM formLeave WHERE (DepartmentID IN  (SELECT catDepartment.DeptID " +
                       "FROM catDepartment INNER JOIN formEmployees ON catDepartment.DeptID = formEmployees.DeptID " +
                        "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
            }
            else if (usrlvl == "Dean" || usrlvl == "Director")
            {
                sqlc = "SELECT  LeaveID FROM formLeave " +
                    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN " +
                    "formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
                    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (DeanApproval IS NULL) AND (HeadApproval = 'Approved')";
            }
            else
            {
                sqlc = "SELECT  LeaveID FROM formLeave WHERE (HRApproval IS NULL) AND (DeanApproval = 'Approved')";
            }


            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        int cbanotif(string empid, string usrlvl)
        {
            string sqlc;

            sqlc = "SELECT  formCBAID FROM formCBA WHERE (HRApproval IS NULL)";



            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        int flexnotif(string empid, string usrlvl)
        {
            string sqlc;
            if (usrlvl == "Head" || usrlvl == "Chairperson")
            {
                sqlc = "SELECT FlexiID FROM formFlexi WHERE (DeptID IN  (SELECT catDepartment.DeptID " +
                       "FROM catDepartment INNER JOIN formEmployees ON catDepartment.DeptID = formEmployees.DeptID " +
                        "WHERE (formEmployees.EmployeeID = " + empid + "))) AND (HeadApproval IS NULL)";
            }
            else if (usrlvl == "Dean" || usrlvl == "Director")
            {
                sqlc = "SELECT  FlexiID FROM formFlexi " +
                    "WHERE (CollegeID IN (SELECT catCollege.CollegeID FROM catCollege INNER JOIN " +
                    "formEmployees ON catCollege.CollegeID = formEmployees.CollegeID" +
                    " WHERE (formEmployees.EmployeeID = " + empid + "))) AND (DeanApproval IS NULL) AND (HeadApproval = 'Approved')";
            }
            else
            {
                sqlc = "SELECT  FlexiID FROM formFlexi WHERE (HRApproval IS NULL) AND (DeanApproval = 'Approved')";
            }


            SqlDataAdapter cmd = new SqlDataAdapter(sqlc, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows.Count;
        }

        string approvepos(string empid)
        {
            string scmd = "SELECT catPositions.catPositionsName FROM catPositions INNER JOIN " +
                    "formEmployees ON catPositions.catPositionsID = formEmployees.catPositionsID " +
                    "WHERE formEmployees.EmployeeID = " + empid;

            SqlDataAdapter cmd = new SqlDataAdapter(scmd, conn);

            DataTable dtbl = new DataTable();

            cmd.Fill(dtbl);

            return dtbl.Rows[0][0].ToString();
        }

    }
}